package tt.dz.biz.dao.batchpay;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;
import tt.dz.biz.dao.BaseDAO;
import tt.dz.common.LogUtil;
import tt.dz.vo.BatchPayVO;
import tt.dz.vo.OrderServiceVO;
import tt.dz.vo.TranInfoVO;

@Repository("batchPayDAO")
public class BatchPayDAOImp
  extends BaseDAO
{
  private static final String QUERY_TRAN_INFO_BYPAYER = "select payer,receiver,service_no as serviceNo,sum(payment_amount) as total_amount,count(1) as total_count  from  MS_TRAN_INFO   where pay_type_code in (2,3)  and pay_state_code=2 and batch_no is null  and payer=:payer and  accounting_date < :endTime  group by payer,receiver,service_no ";
  private static final String QUERY_MAX_BATCH_NO = "select max(batch_no) max_batch_no  from ms_batch_pay where payer=:payer and receiver = :receiver  and service_no=:serviceNo ";
  private static final String INSERT_BATCH_PAY = "insert into ms_batch_pay(batch_no,payer,receiver,created_time,accounting_date,total_count,total_fee,state,batch_month,reason,service_no)  values(:batchNo,:payer,:receiver,:createdTime,:accountingDate,:totalCount,:totalFee,:state,:batchMonth,:reason,:serviceNo )";
  public static final String QUERY_MS_BATCH_PAY = "select bp.batch_no,bp.service_no,osi.name as service_name,bp.payer,bp.receiver,bp.created_time,bp.accounting_date,bp.total_count,bp.total_fee,bp.state,bp.reason,mm.name as  payerName,mm2.name as receiverName,bp.batch_month  from ms_batch_pay bp , ms_merchant mm ,ms_merchant mm2 ,order_service_info osi where bp.payer = mm.merchant_code and bp.receiver = mm2.merchant_code and bp.service_no=osi.serviceno";
  public static final String UPDATE_TRAN_INFO_BYPAYER = "update MS_TRAN_INFO set batch_no=:batchNo  where batch_no is null and payer=:payer and receiver = :receiver  and pay_type_code in (2,3)  and pay_state_code=2 and accounting_date < :accountingDate ";
  public static final String QUERY_MS_BATCH_PAY_PAYER = "select DISTINCT payer from MS_TRAN_INFO where pay_type_code in (2,3)  and pay_state_code=2 and batch_no is null  and  accounting_date < :endTime  ";
  public static final String QUERY_MS_MERCHANT_NAME = " select name from ms_merchant where merchant_code=:merchantCode ";
  public static final String QUERY_ORDERNO_FOR_BATCH_PAY = "select  order_no from MS_TRAN_INFO ti where ti.receiver=:receiver and payer=:payer  and batch_no=:batchNo and pay_state_code=2";
  public static final String QUERY_MS_BATCH_PAY_PAYER_FOR_WEB = "select DISTINCT payer from ms_batch_pay  ";
  private static final String QUERY_SETTLEMENT_SERVICE_FOR_BATCHPAY = " select osi.serviceno , osi.name from ms_assets_lblts_account_rule mal ,order_service_info osi   where mal.service_no = osi.serviceno and osi.isvirtual = 1 and osi.isvalid = 1 and  mal.sub_subject_code in (  select mal.sub_subject_code from ms_assets_lblts_account_rule mal ,ms_subject ms  where  mal.sub_subject_code = ms.subject_code and ms.parent_subject_code='1122'  and mal.service_no=:serviceNo)";
  private static final String UPDATE_RECEIVABLE_BATCH_PAY = "update  ms_batch_pay  set trade_no=:tradeNo,\tpay_time= CURRENT_TIMESTAMP , pay_account_date =:payAccountDate,\tstate=:state,updated_by=:updatedBy  where batch_no=:batchNo  and \tpayer=:payer  and \treceiver=:receiver  and \tservice_no=:serviceNo ";
  
  public String queryMerchantName(String merchantCode)
    throws Exception
  {
    Map<String, String> paramMap = new HashMap();
    paramMap.put("merchantCode", merchantCode);
    try
    {
      return (String)this.jdbcTemplate.queryForObject(" select name from ms_merchant where merchant_code=:merchantCode ", paramMap, String.class);
    }
    catch (Exception e)
    {
      LogUtil.debug("查询运营商名称为空" + e.toString());
    }
    return null;
  }
  
  public List<String> queryBatchPayPayerSel(Date endTime)
    throws Exception
  {
    String sql = "select DISTINCT payer from MS_TRAN_INFO where pay_type_code in (2,3)  and pay_state_code=2 and batch_no is null  and  accounting_date < :endTime  ";
    MapSqlParameterSource parameters = new MapSqlParameterSource();
    parameters.addValue("endTime", endTime);
    return querySingleColumnForList(sql, parameters, String.class);
  }
  
  public Integer getMaxBatchNo(String payer, String receiver, String serviceNo)
    throws Exception
  {
    MapSqlParameterSource parameters = new MapSqlParameterSource();
    parameters.addValue("payer", payer);
    parameters.addValue("receiver", receiver);
    parameters.addValue("serviceNo", serviceNo);
    List<Map<String, Object>> tempBatchNoList = queryForList("select max(batch_no) max_batch_no  from ms_batch_pay where payer=:payer and receiver = :receiver  and service_no=:serviceNo ", parameters);
    if (tempBatchNoList.size() > 0)
    {
      Map<String, Object> batchNoRow = (Map)tempBatchNoList.get(0);
      return Integer.valueOf(batchNoRow.get("maxBatchNo") != null ? ((Integer)batchNoRow.get("maxBatchNo")).intValue() : 0);
    }
    return Integer.valueOf(0);
  }
  
  public List<Map<String, Object>> getTranInfoByPayer(String payer, Date endTime)
    throws Exception
  {
    MapSqlParameterSource parameters = new MapSqlParameterSource();
    parameters.addValue("payer", payer);
    parameters.addValue("endTime", endTime);
    List<Map<String, Object>> tranInfoList = queryForList("select payer,receiver,service_no as serviceNo,sum(payment_amount) as total_amount,count(1) as total_count  from  MS_TRAN_INFO   where pay_type_code in (2,3)  and pay_state_code=2 and batch_no is null  and payer=:payer and  accounting_date < :endTime  group by payer,receiver,service_no ", parameters);
    return tranInfoList;
  }
  
  public int insertBatchPay(BatchPayVO batchPayVo)
    throws Exception
  {
    return save("insert into ms_batch_pay(batch_no,payer,receiver,created_time,accounting_date,total_count,total_fee,state,batch_month,reason,service_no)  values(:batchNo,:payer,:receiver,:createdTime,:accountingDate,:totalCount,:totalFee,:state,:batchMonth,:reason,:serviceNo )", batchPayVo);
  }
  
  public int[] insertBatchBatchPay(List<BatchPayVO> batchPayList)
    throws Exception
  {
    return batchSave("insert into ms_batch_pay(batch_no,payer,receiver,created_time,accounting_date,total_count,total_fee,state,batch_month,reason,service_no)  values(:batchNo,:payer,:receiver,:createdTime,:accountingDate,:totalCount,:totalFee,:state,:batchMonth,:reason,:serviceNo )", batchPayList);
  }
  
  public int[] updateTranInfoByPayer(List<TranInfoVO> tranInfoList)
    throws Exception
  {
    return batchSave("update MS_TRAN_INFO set batch_no=:batchNo  where batch_no is null and payer=:payer and receiver = :receiver  and pay_type_code in (2,3)  and pay_state_code=2 and accounting_date < :accountingDate ", tranInfoList);
  }
  
  public List<BatchPayVO> queryBatchPay(Map<String, Object> parameter)
    throws Exception
  {
    StringBuilder sql = new StringBuilder("select bp.batch_no,bp.service_no,osi.name as service_name,bp.payer,bp.receiver,bp.created_time,bp.accounting_date,bp.total_count,bp.total_fee,bp.state,bp.reason,mm.name as  payerName,mm2.name as receiverName,bp.batch_month  from ms_batch_pay bp , ms_merchant mm ,ms_merchant mm2 ,order_service_info osi where bp.payer = mm.merchant_code and bp.receiver = mm2.merchant_code and bp.service_no=osi.serviceno");
    MapSqlParameterSource parameters = new MapSqlParameterSource(parameter);
    if (StringUtils.isNotBlank((String)parameter.get("payer"))) {
      sql.append(" and bp.payer=:payer ");
    }
    if (StringUtils.isNotBlank((String)parameter.get("batchMonth"))) {
      sql.append(" and bp.batch_month=:batchMonth ");
    }
    return queryForList(sql.toString(), parameters, BatchPayVO.class);
  }
  
  public List<String> queryBatchPayerForWaitForPay()
  {
    StringBuilder sql = new StringBuilder("select DISTINCT payer from ms_batch_pay  ");
    sql.append("  where state=2");
    return querySingleColumnForList(sql.toString(), null, String.class);
  }
  
  public List<OrderServiceVO> querySettlementServiceForBatchPay(String serviceNo)
  {
    MapSqlParameterSource param = new MapSqlParameterSource();
    param.addValue("serviceNo", serviceNo);
    return queryForList(" select osi.serviceno , osi.name from ms_assets_lblts_account_rule mal ,order_service_info osi   where mal.service_no = osi.serviceno and osi.isvirtual = 1 and osi.isvalid = 1 and  mal.sub_subject_code in (  select mal.sub_subject_code from ms_assets_lblts_account_rule mal ,ms_subject ms  where  mal.sub_subject_code = ms.subject_code and ms.parent_subject_code='1122'  and mal.service_no=:serviceNo)", param, OrderServiceVO.class);
  }
  
  public int updateReceivableForBatchPay(BatchPayVO batchPayVO)
  {
    return save("update  ms_batch_pay  set trade_no=:tradeNo,\tpay_time= CURRENT_TIMESTAMP , pay_account_date =:payAccountDate,\tstate=:state,updated_by=:updatedBy  where batch_no=:batchNo  and \tpayer=:payer  and \treceiver=:receiver  and \tservice_no=:serviceNo ", batchPayVO);
  }
  
  public List<String> queryOrderNoForPaying(BatchPayVO batchPayVO)
  {
    SqlParameterSource sqlParameters = new BeanPropertySqlParameterSource(batchPayVO);
    return querySingleColumnForList("select  order_no from MS_TRAN_INFO ti where ti.receiver=:receiver and payer=:payer  and batch_no=:batchNo and pay_state_code=2", sqlParameters, String.class);
  }
}
